Freelog Special Freeware 31/FreelogHS31.iso/ArgentCompta/FASTCAISSE/Setup FASTCAISSE.exe/{app}/CHANGE LG DESI.sql

Text File | 2007-01-27 | 14KB | 444 lines

DROP PROCEDURE "GET_PURGE";
DROP PROCEDURE "INTERROGATION_PONTA";
DROP PROCEDURE "INTERROGATION_PONTV";
DROP PROCEDURE "GET_DESIGNATION";
ALTER TABLE DESIGNATIONS ALTER TEXTE TYPE VARCHAR(60);

CREATE PROCEDURE "GET_PURGE"
(
  "TYPEPURGE" SMALLINT,
  "RCH_TEMP" SMALLINT
)
RETURNS
(
  "INDXOUT" INTEGER,
  "DESI" VARCHAR(60)
)
AS
BEGIN
IF (TYPEPURGE=1) THEN
FOR SELECT INDX,NOM||' '||PRENOM  FROM CLIENTS WHERE TEMP=:RCH_TEMP ORDER BY NOM INTO :INDXOUT,:DESI DO SUSPEND;
ELSE IF (TYPEPURGE=2) THEN
FOR SELECT INDX,NOM||' '||PRENOM  FROM FOURNISSEURS WHERE TEMP=:RCH_TEMP ORDER BY NOM INTO :INDXOUT,:DESI DO SUSPEND;
ELSE IF (TYPEPURGE=3) THEN
FOR SELECT INDX,D.TEXTE FROM ARTICLES A LEFT JOIN DESIGNATIONS D ON D.INDXART=A.INDX AND CODELANGUE=1 WHERE TEMP=:RCH_TEMP ORDER BY 2 INTO :INDXOUT,:DESI DO SUSPEND;
END
 ;

CREATE PROCEDURE "GET_DESIGNATION"
(
  "INDX" INTEGER,
  "CODELANGUE" SMALLINT
)
RETURNS
(
  "TEXTE" VARCHAR(60),
  "RCODE" SMALLINT
)
AS
DECLARE VARIABLE V1 SMALLINT;
BEGIN
RCODE = :CODELANGUE;
SELECT COUNT(*) FROM DESIGNATIONS D WHERE D.INDXART=:INDX AND D.CODELANGUE=:RCODE INTO V1;
IF (V1 = 0) THEN
  BEGIN
  RCODE = 1;
  SELECT COUNT(*) FROM DESIGNATIONS D WHERE D.INDXART=:INDX AND D.CODELANGUE=1 INTO V1;
  END
SELECT TEXTE FROM DESIGNATIONS D WHERE D.INDXART=:INDX AND D.CODELANGUE=:RCODE INTO :TEXTE;
SUSPEND;
END
 ;

CREATE PROCEDURE "INTERROGATION_PONTA"
(
  "DATEDEB" DATE,
  "DATEFIN" DATE,
  "DETAIL_VERSEMENTS" SMALLINT,
  "DETAIL_TVA" SMALLINT,
  "DETAIL_TVA_PORT" SMALLINT,
  "DETAIL_HT" SMALLINT
)
RETURNS
(
  "DATEPIECE" TIMESTAMP,
  "NOPIECE" VARCHAR(15),
  "NOLIGNE" SMALLINT,
  "INFO1" VARCHAR(15),
  "INFO2" VARCHAR(15),
  "LIBELLE" VARCHAR(85),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "ESTPAYE" CHAR(1),
  "ESTESP" CHAR(1),
  "INDX" INTEGER,
  "BLOBLIGNE" BLOB SUB_TYPE 1 SEGMENT SIZE 80
)
AS
DECLARE VARIABLE TTC DECIMAL(12,2);
DECLARE VARIABLE M1 DECIMAL(15,2);
DECLARE VARIABLE TVA DECIMAL(12,2);
DECLARE VARIABLE HT DECIMAL(12,2);
DECLARE VARIABLE NOTIERS INTEGER;
DECLARE VARIABLE ST VARCHAR(200);
DECLARE VARIABLE TYPEPIECE VARCHAR(35);
DECLARE VARIABLE TYPEVERS VARCHAR(35);
DECLARE VARIABLE TAUX DECIMAL(6,2);
DECLARE VARIABLE TAUXTVA INTEGER;
DECLARE VARIABLE TVAPORT INTEGER;
DECLARE VARIABLE PORT DECIMAL(12,2);
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE REDUC DECIMAL(5,2);
BEGIN
FOR SELECT P.INDX,P.DATEPIECE,P.NOPIECE,P.TOTALTVA,P.TOTALTTC,P.NOTIERS,P.TVAPORT,
    P.PORT,P.PCREDUC+P.PCESCOMPTE,
    T.COMPTGENE,T.COMPTAUXI,C.DESIGNATION
    FROM PIECES P
    LEFT JOIN FOURNISSEURS T ON T.INDX=P.NOTIERS
    LEFT JOIN CHAINES C on (c.TYPEC=0) AND (c.LIGNE=p.TYPEPIECE) AND (C.COLONNE=0)
    WHERE P.TOTALTTC<>0 AND P.DATEPONT IS NULL AND P.DATEPIECE>=:DATEDEB
    AND P.DATEPIECE<:DATEFIN AND TYPEPIECE IN (13,14)
    ORDER BY P.DATEPIECE
    INTO :INDX,:DATEPIECE,:NOPIECE,:TVA,:TTC,:NOTIERS,:TVAPORT,:PORT,:REDUC,
    :INFO1,:INFO2,:TYPEPIECE DO
  BEGIN
  HT = TTC - TVA;
  NOLIGNE=1;
  IF (INFO1 IS NULL OR INFO1='') THEN INFO1='401000';
  SELECT SUM(MONTANT) FROM VERSEMENTS WHERE INDEXPIECE=:INDX INTO :M1;
  IF (TTC=M1) THEN ESTPAYE = 'O'; ELSE ESTPAYE = 'N';
  SELECT SUM(MONTANT) FROM VERSEMENTS WHERE INDEXPIECE=:INDX AND TYPEV=0 INTO :M1;
  IF (TTC=M1) THEN ESTESP = 'O'; ELSE ESTESP = 'N';
  IF (NOTIERS>0) THEN SELECT AVALUE FROM GET_NOM_FOUR(:NOTIERS) INTO :ST;
                 ELSE ST = 'N░ ' || NOPIECE;
  /* LIGNE 1 (Montant TTC) */
  IF (DETAIL_VERSEMENTS<>0) THEN
    BEGIN
    /* DΘtailler les versements */
    FOR SELECT C.DESIGNATION,V.MONTANT FROM VERSEMENTS V
        LEFT JOIN CHAINES C on (c.TYPEC=22) AND (c.LIGNE=V.TYPEV) AND (C.COLONNE=0)
        WHERE V.INDEXPIECE=:INDX AND V.MONTANT<>0
        ORDER BY V.DATEV
        INTO :TYPEVERS,:M1 DO
      BEGIN
      TTC = TTC-M1;
      CREDIT=NULL;
      DEBIT=NULL;
      IF (M1<0) THEN DEBIT=-M1; ELSE CREDIT=M1;
      LIBELLE = TYPEVERS || ' ' || TYPEPIECE || ' ' || ST;
      SUSPEND;
      END
    END
  CREDIT=NULL;
  DEBIT=NULL;
  IF(TTC<>0) THEN
    BEGIN
    LIBELLE = TYPEPIECE || ' ' || ST;
    IF (TTC<0) THEN DEBIT=-TTC; ELSE CREDIT=TTC;
    SUSPEND;
    END

  /*
  * LIGNE 2
  * (TVA)
  */
  NOLIGNE=2;
  INFO1 = '445660';
  INFO2 = '';
  IF (TVA<>0) THEN
    BEGIN
    /* Faire d'abord le port */
    IF (DETAIL_TVA_PORT=1 AND PORT<>0) THEN
      BEGIN
      LIBELLE = 'TVA ' || CAST(CAST(TVAPORT AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
      M1 = (PORT * TVAPORT /10000);
      TVA = TVA - M1;
      IF (M1>0) THEN DEBIT=M1; ELSE CREDIT=-M1;
      INFO2 = 'PORT';
      SUSPEND;
      END
    /* DΘtailler TVA avec un seul taux revient α ne pas dΘtailler la TVA */
    I=0;
    FOR SELECT DISTINCT TVA FROM LIGNES L
    WHERE INDEXPIECE=:INDX AND TVA<>0 INTO :TAUXTVA DO I=I+1;
    /* Ensuite le cas simple */
    IF ((DETAIL_TVA=0) or (i=1) ) THEN
      BEGIN
      /* Ne pas dΘtailler plusieurs taux
      /* vΘrifier si un seul taux dans la piΦce
      ce qui permet de faire un libellΘ prΘcis */
      if (I=1) then LIBELLE = 'TVA ' || CAST(CAST(TAUXTVA AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
      ELSE LIBELLE = 'TVA';
      CREDIT=NULL;
      DEBIT=NULL;
      IF (TVA>0) THEN DEBIT=TVA; ELSE CREDIT=-TVA;
      INFO2 = '';
      SUSPEND;
      END /* IF */
    ELSE
      BEGIN
      /* Cas compliquΘ: Calculer ligne par ligne.... */
      FOR SELECT TVA,SUM(l.PRIX*l.QUANTITE*((100-(L.REMISE+:REDUC))/100)*TVA/10000) FROM LIGNES L
          WHERE INDEXPIECE=:INDX AND TVA<>0
          GROUP BY TVA INTO :TAUXTVA,:M1 DO
        BEGIN
        CREDIT=NULL;
        DEBIT=NULL;
        IF (ABS(TVA-M1)<0.03) THEN M1=TVA;
        LIBELLE = 'TVA ' || CAST(CAST(TAUXTVA AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
        TVA = TVA - M1;
        IF (M1>0) THEN DEBIT=M1; ELSE CREDIT=-M1;
        INFO2 = TAUXTVA;
        SUSPEND;
        END
        /* Le reste de la TVA */
      IF (TVA<>0) THEN
        /* ProblΦme d'arrondi */
        BEGIN
        CREDIT=NULL;
        DEBIT=NULL;
        LIBELLE = 'Ecart sur calcul TVA par lignes';
        INFO2 = 'ERREUR';
        IF (TVA>0) THEN DEBIT=TVA; ELSE CREDIT=-TVA;
        SUSPEND;
        END
      END /* dΘtail*/
    END /* tva <>0 */
  /*
  * LIGNE 3
  * (LIGNES DE FACTURE HT)
  */
  NOLIGNE=3;
  INFO1 = '';
  INFO2 = '';
  LIBELLE = '';
  FOR SELECT LIBELLE,l.PRIX*l.QUANTITE*((100-(L.REMISE+:REDUC))/100) AS TOT,L.TVA,
      R.CPTACHAT,R.CPTIMPORT
      FROM LIGNES L
      LEFT JOIN ARTICLES A ON A.INDX=L.INDEXARTICLE
      LEFT JOIN DESIGNATIONS D ON D.INDXART=L.INDEXARTICLE AND D.CODELANGUE=1
      LEFT JOIN RAYONS R ON R.PKEY=A.PKEYR
      WHERE L.INDEXPIECE=:INDX AND PRIX<>0 AND QUANTITE<>0
      ORDER BY 2 DESC
      INTO :BLOBLIGNE,:M1,:TAUXTVA,:INFO1,:ST DO IF (HT<>0) THEN
    BEGIN
    CREDIT=NULL;
    DEBIT=NULL;
    IF (TAUXTVA=0) THEN INFO1 = ST; /* utiliser compte import au lieu de compte achat */
    IF ((DETAIL_HT=0) OR (ABS(HT-M1)<0.03)) THEN M1 = HT; /* si pas de dΘtail: prendre seulement 1 compte */
    HT = HT - M1;
    IF (M1>0) THEN DEBIT=M1; ELSE CREDIT=-M1;
    SUSPEND;
    END
  BLOBLIGNE = NULL;
  IF (HT<>0) THEN
    /* ProblΦme d'arrondi */
    BEGIN
    CREDIT=NULL;
    DEBIT=NULL;
    LIBELLE = 'Ecart sur calcul HT par lignes';
    INFO2 = 'ERREUR';
    IF (HT>0) THEN DEBIT=HT; ELSE CREDIT=-HT;
    SUSPEND;
    END
  END
END
 ;


CREATE PROCEDURE "INTERROGATION_PONTV"
(
  "DATEDEB" DATE,
  "DATEFIN" DATE,
  "DETAIL_VERSEMENTS" SMALLINT,
  "DETAIL_TVA" SMALLINT,
  "DETAIL_TVA_PORT" SMALLINT,
  "DETAIL_HT" SMALLINT
)
RETURNS
(
  "DATEPIECE" TIMESTAMP,
  "NOPIECE" VARCHAR(15),
  "NOLIGNE" SMALLINT,
  "INFO1" VARCHAR(15),
  "INFO2" VARCHAR(15),
  "LIBELLE" VARCHAR(85),
  "DEBIT" DECIMAL(12, 2),
  "CREDIT" DECIMAL(12, 2),
  "ESTPAYE" CHAR(1),
  "ESTESP" CHAR(1),
  "INDX" INTEGER,
  "BLOBLIGNE" BLOB SUB_TYPE 1 SEGMENT SIZE 80
)
AS
DECLARE VARIABLE TTC DECIMAL(12,2);
DECLARE VARIABLE M1 DECIMAL(15,2);
DECLARE VARIABLE TVA DECIMAL(12,2);
DECLARE VARIABLE HT DECIMAL(12,2);
DECLARE VARIABLE NOTIERS INTEGER;
DECLARE VARIABLE ST VARCHAR(200);
DECLARE VARIABLE TYPEPIECE VARCHAR(35);
DECLARE VARIABLE TYPEVERS VARCHAR(35);
DECLARE VARIABLE TAUX DECIMAL(6,2);
DECLARE VARIABLE TAUXTVA INTEGER;
DECLARE VARIABLE TVAPORT INTEGER;
DECLARE VARIABLE PORT DECIMAL(12,2);
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE REDUC DECIMAL(5,2);
BEGIN
FOR SELECT P.INDX,P.DATEPIECE,P.NOPIECE,P.TOTALTVA,P.TOTALTTC,P.NOTIERS,P.TVAPORT,
    P.PORT,P.PCREDUC+P.PCESCOMPTE,
    T.COMPTGENE,T.COMPTAUXI,C.DESIGNATION
    FROM PIECES P
    LEFT JOIN CLIENTS T ON T.INDX=P.NOTIERS
    LEFT JOIN CHAINES C on (c.TYPEC=0) AND (c.LIGNE=p.TYPEPIECE) AND (C.COLONNE=0)
    WHERE P.TOTALTTC<>0 AND P.DATEPONT IS NULL AND P.DATEPIECE>=:DATEDEB
    AND P.DATEPIECE<:DATEFIN AND z<>0
    ORDER BY P.DATEPIECE
    INTO :INDX,:DATEPIECE,:NOPIECE,:TVA,:TTC,:NOTIERS,:TVAPORT,:PORT,:REDUC,
    :INFO1,:INFO2,:TYPEPIECE DO
  BEGIN
  HT = TTC - TVA;
  NOLIGNE=1;
  IF (INFO1 IS NULL OR INFO1='') THEN INFO1='411000';
  SELECT SUM(MONTANT) FROM VERSEMENTS WHERE INDEXPIECE=:INDX INTO :M1;
  IF (TTC=M1) THEN ESTPAYE = 'O'; ELSE ESTPAYE = 'N';
  SELECT SUM(MONTANT) FROM VERSEMENTS WHERE INDEXPIECE=:INDX AND TYPEV=0 INTO :M1;
  IF (TTC=M1) THEN ESTESP = 'O'; ELSE ESTESP = 'N';
  IF (NOTIERS>0) THEN SELECT AVALUE FROM GET_NOM_CLIENT(:NOTIERS) INTO :ST;
                 ELSE ST = 'N░ ' || NOPIECE;
  /* LIGNE 1 (Montant TTC) */
  IF (DETAIL_VERSEMENTS<>0) THEN
    BEGIN
    /* DΘtailler les versements */
    FOR SELECT C.DESIGNATION,V.MONTANT FROM VERSEMENTS V
        LEFT JOIN CHAINES C on (c.TYPEC=22) AND (c.LIGNE=V.TYPEV) AND (C.COLONNE=0)
        WHERE V.INDEXPIECE=:INDX AND V.MONTANT<>0
        ORDER BY V.DATEV
        INTO :TYPEVERS,:M1 DO
      BEGIN
      TTC = TTC-M1;
      CREDIT=NULL;
      DEBIT=NULL;
      IF (M1>0) THEN DEBIT=M1; ELSE CREDIT=-M1;
      LIBELLE = TYPEVERS || ' ' || TYPEPIECE || ' ' || ST;
      SUSPEND;
      END
    END
  CREDIT=NULL;
  DEBIT=NULL;
  IF(TTC<>0) THEN
    BEGIN
    LIBELLE = TYPEPIECE || ' ' || ST;
    IF (TTC>0) THEN DEBIT=TTC; ELSE CREDIT=-TTC;
    SUSPEND;
    END

  /*
  * LIGNE 2
  * (TVA)
  */
  NOLIGNE=2;
  INFO1 = '445660';
  INFO2 = '';
  IF (TVA<>0) THEN
    BEGIN
    /* Faire d'abord le port */
    IF (DETAIL_TVA_PORT=1 AND PORT<>0) THEN
      BEGIN
      LIBELLE = 'TVA ' || CAST(CAST(TVAPORT AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
      M1 = (PORT * TVAPORT /10000);
      TVA = TVA - M1;
      IF (M1<0) THEN DEBIT=-M1; ELSE CREDIT=M1;
      INFO2 = 'PORT';
      SUSPEND;
      END
    /* DΘtailler TVA avec un seul taux revient α ne pas dΘtailler la TVA */
    I=0;
    FOR SELECT DISTINCT TVA FROM LIGNES L
      WHERE INDEXPIECE=:INDX AND TVA<>0 INTO :TAUXTVA DO I = I + 1;
    /* Ensuite le cas simple */
    IF ((DETAIL_TVA=0) or (i=1) ) THEN
      BEGIN
      /* Ne pas dΘtailler plusieurs taux
      /* vΘrifier si un seul taux dans la piΦce
      ce qui permet de faire un libellΘ prΘcis */
      if (I=1) then LIBELLE = 'TVA ' || CAST(CAST(TAUXTVA AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
      ELSE LIBELLE = 'TVA';
      CREDIT=NULL;
      DEBIT=NULL;
      IF (TVA<0) THEN DEBIT=-TVA; ELSE CREDIT=TVA;
      INFO2 = '';
      SUSPEND;
      END /* IF */
    ELSE
      BEGIN
      /* Cas compliquΘ: Calculer ligne par ligne.... */
      FOR SELECT TVA,SUM(l.PRIX*l.QUANTITE*((100-(L.REMISE+:REDUC))/100)*TVA/10000) FROM LIGNES L
          WHERE INDEXPIECE=:INDX AND TVA<>0
          GROUP BY TVA INTO :TAUXTVA,:M1 DO
        BEGIN
        CREDIT=NULL;
        DEBIT=NULL;
        IF (ABS(TVA-M1)<0.03) THEN M1=TVA;
        LIBELLE = 'TVA ' || CAST(CAST(TAUXTVA AS DECIMAL(6,2)) / 100 AS VARCHAR(8)) || '%';
        TVA = TVA - M1;
        IF (M1<0) THEN DEBIT=-M1; ELSE CREDIT=M1;
        INFO2 = TAUXTVA;
        SUSPEND;
        END
        /* Le reste de la TVA */
      IF (TVA<>0) THEN
        /* ProblΦme d'arrondi */
        BEGIN
        CREDIT=NULL;
        DEBIT=NULL;
        LIBELLE = 'Ecart sur calcul TVA par lignes';
        INFO2 = 'ERREUR';
        IF (TVA<0) THEN DEBIT=-TVA; ELSE CREDIT=TVA;
        SUSPEND;
        END
      END /* dΘtail*/
    END /* tva <>0 */
  /*
  * LIGNE 3
  * (LIGNES DE FACTURE HT)
  */
  NOLIGNE=3;
  INFO1 = '';
  INFO2 = '';
  LIBELLE = '';
  FOR SELECT L.LIBELLE,l.PRIX*l.QUANTITE*((100-(L.REMISE+:REDUC))/100) AS TOT,L.TVA,
      R.CPTVENTE,R.CPTEXPORT
      FROM LIGNES L
      LEFT JOIN ARTICLES A ON A.INDX=L.INDEXARTICLE
      LEFT JOIN DESIGNATIONS D ON D.INDXART=L.INDEXARTICLE AND D.CODELANGUE=1
      LEFT JOIN RAYONS R ON R.PKEY=A.PKEYR
      WHERE L.INDEXPIECE=:INDX AND PRIX<>0 AND QUANTITE<>0
      ORDER BY 2 DESC
      INTO :BLOBLIGNE,:M1,:TAUXTVA,:INFO1,:ST DO IF (HT<>0) THEN
    BEGIN
    CREDIT=NULL;
    DEBIT=NULL;
    IF (TAUXTVA=0) THEN INFO1 = ST; /* utiliser compte import au lieu de compte achat */
    IF ((DETAIL_HT=0) OR (ABS(HT-M1)<0.03)) THEN M1 = HT; /* si pas de dΘtail: prendre seulement 1 compte */
    HT = HT - M1;
    IF (M1<0) THEN DEBIT=-M1; ELSE CREDIT=M1;
    SUSPEND;
    END
  BLOBLIGNE = NULL;
  IF (HT<>0) THEN
    /* ProblΦme d'arrondi */
    BEGIN
    CREDIT=NULL;
    DEBIT=NULL;
    LIBELLE = 'Ecart sur calcul HT par lignes';
    INFO2 = 'ERREUR';
    IF (HT<0) THEN DEBIT=-HT; ELSE CREDIT=HT;
    SUSPEND;
    END
  END
END
 ;